package models;


import java.text.SimpleDateFormat;
import java.util.*;

import models.*;
import javax.persistence.*;
import javax.persistence.criteria.CriteriaBuilder.In;

import com.avaje.ebean.Ebean;
import com.avaje.ebean.Expr;
import com.avaje.ebean.Expression;
import com.avaje.ebean.ExpressionList;
import com.avaje.ebean.FetchConfig;
import com.avaje.ebean.Query;
import com.avaje.ebean.RawSql;
import com.avaje.ebean.RawSqlBuilder;
import com.avaje.ebean.validation.NotNull;

import play.db.ebean.*;
import play.data.format.*;
import play.data.validation.*;


/**
 * User entity managed by Ebean
 */
@Entity
@Table(name="doctor_details")
public class DoctorDetails extends Model {

	public static final long serialVersionUID = 1L;

	@Id
	@Column(name="iddoctor_details")
	public long id;

   @OneToOne
    @JoinColumn(name="doctor_id")
    public User doctor;

    @OneToOne
    @JoinColumn(name="idspeciality")
    public Specialities specialities;
    
    @Column(name="confirmed")
    public String confirmed="N";

    @Column(name="registration_no")
    public String registrationNo;

    /*@OneToMany(cascade=CascadeType.ALL)
    @JoinColumn(name="iddoctor_details" , referencedColumnName ="iddoctor_details")
    public List<DoctorHospital> hospitals;  
*/    
    
    // -- Queries

    public static Model.Finder<Long,DoctorDetails> find = new Model.Finder(Long.class, DoctorDetails.class);

    public static List<DoctorDetails> getHospitalsForDoctor(long doctorId) {    
    	List<DoctorDetails> listOfHospitals =find
    												.join("doctor")
    												.fetch("hospitals")
    												.where().eq("id",doctorId)
    												.findList();
    	return listOfHospitals;
    			
    }
    
    public static List<DoctorDetails> getDoctors(String speciality, String doctorName, Integer day,String hospital,String city) {
    	List<DoctorDetails> doctors = new ArrayList<DoctorDetails>();
    	ExpressionList<DoctorDetails>	doctorEx=	find
							.join("doctor")
							.fetch("specialities")
							.fetch("hospitals")    
							.where()
							.eq("approved", "Y");
    		
    		if(!"".equals(speciality)){
    			doctorEx.eq("speciality", speciality);
    		}
    		StringBuilder s = new StringBuilder();
    	   	if(!"".equals(hospital) ){
    	   		s.append("hospital_name ='"+hospital+"'");
        	}
        	if(!"".equals(city)){
          		if( s.length() >0){
        			s .append(" and ");
        		}
        		s.append( " hospital_city = '"+city+"'");
        	}
        	if(s.length()>0){
        		List<Hospitals> hospitalList=Hospitals.find.where().add(Expr.raw(s.toString())).findList();
        		List<Long> l= new ArrayList<Long>();
        		for(Hospitals h:hospitalList){
        			l.add(h.id);
        		}
        		doctorEx.in("hospital_id", l);
        	}
    		if(!"".equals(doctorName)){
    			doctorEx.eq("name", doctorName);
    		}
    		if(day >0){
    			doctorEx
    			.filterMany("schedules").eq("day", day);
    		}

    		doctors= doctorEx.findList();
    	return doctors;
    			
    }

    public static List<DoctorDetails> getDoctorName(String name, String speciality) {    
    	List<DoctorDetails> doctors= new ArrayList<DoctorDetails>();
    	if(null != speciality && speciality.length()>0){
    		doctors=find.join("doctor")
    					.fetch("specialities")
    					.where()
    					.eq("speciality", speciality)
    					.like("upper(name)", "%"+name.toUpperCase()+"%")
    					.findList();
    	}else{
    		doctors=find.join("doctor")
    				.where()
    				.like("upper(name)", "%"+name.toUpperCase()+"%")
    				.findList();
    	}
    	return doctors;
    			
    }
    
    public static List<DoctorDetails> getDoctorScheduleDay(Long doctorId, Long day) {    
    	List<DoctorDetails> doctorSchedule =find
    												.join("doctor")
    												.where()
    												.eq("doctor_id",doctorId.intValue())
    												.eq("day", day.intValue())
    												.findList();
    	return doctorSchedule;
    			
    }

    public static DoctorDetails getDoctorDetails(long doctorId) {    
    	DoctorDetails doctorSchedule =find
    												.join("doctor")
    												.fetch("specialities")
    												.where()
    												.eq("doctor_id",doctorId)
    												.findUnique();
    	return doctorSchedule;
    			
    }
    
    public static List<DoctorDetails> getUnapprovedUsers() {
    	ArrayList<String> list = new ArrayList<String>() {{
    	    add("D");
    	    add("S");
    	    add("A");
    	}};
        return find
        		.fetch("doctor")
        		.fetch("specialities")
        		.where()
        		.in("user_type", list)
        		.isNull("approved")
        		.findList();
    }
    
    public static List<DoctorDetails> getHospitalsWithDoctors(Long hospitalId, String speciality,String city) {  
    	StringBuilder s = new StringBuilder();
    	if(null !=hospitalId ){
    		s.append( "hospital_id = "+hospitalId);
    	}
    	if(null !=speciality){
    		if( s.length() >0){
    			s .append(" and ");
    		}
    		s.append( " speciality = '"+speciality+"'");
    	}

    	if(null !=city){
    		if( s.length() >0){
    			s .append(" and ");
    		}
    		s.append( " hospital_city = '"+city+"'");
    	}

    	List<DoctorDetails> listOfHospitals=null;
    	if( s.length() >0){
        	listOfHospitals=find
					.fetch("hospitals")
					.fetch("hospitals.hospital")
					.fetch("specialities")
					.fetch("doctor")
					.where()
					.add(Expr.raw(s.toString()))
					.findList();
    		
    	}else{
        	listOfHospitals=find
					.fetch("hospitals")
					.fetch("hospitals.hospital")
					.fetch("specialities")
					.fetch("doctor")
					.findList();

    	}
    	return listOfHospitals;
    			
    }
    

    public static List<DoctorDetails> getHospitalsWithSpeciality(String speciality,String city) {  
    	StringBuilder s = new StringBuilder();
    	if(null !=speciality){
    		if( s.length() >0){
    			s .append(" and ");
    		}
    		s.append( " speciality = '"+speciality+"'");
    	}

    	if(null !=city){
    		if( s.length() >0){
    			s .append(" and ");
    		}
    		s.append( " hospital_city = '"+city+"'");
    	}

    	List<DoctorDetails> listOfHospitals=null;
    	if( s.length() >0){
        	listOfHospitals=find
					.fetch("hospitals")
					.fetch("hospitals.idHospital")
					.fetch("specialities")
					.where()
					.add(Expr.raw(s.toString()))
					.findList();
    		
    	}else{
        	listOfHospitals=find
					.fetch("hospitals")
					.fetch("hospitals.idHospital")
					.fetch("specialities")
					.findList();

    	}
    	return listOfHospitals;
    			
    }
}

